package com.telsoft.kqxs;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * 
 * @author Do Hong Thang
 * 
 */
public class DBAdapter {

	// Province table
	public static final String PROVINCE_TABLE = "Province";

	public static final String PROVINCE_PRIMARY_KEY = "_id";
	public static final String PROVINCE_CODE = "code";
	public static final String PROVINCE_NAME = "name";

	// Lottery Result
	public static final String LOTTERY_TABLE = "Lottery";
	public static final String LOTTERY_PRIMARY_KEY = "id";

	public static final String OPEN_DATE = "openDate";
	public static final String SPECIAL_1 = "special_1";

	public static final String FIRST = "first";
	public static final String SECOND = "second";
	public static final String THIRD = "third";
	public static final String FOURTH = "fourth";
	public static final String FIFTH = "fifth";
	public static final String SIXTH = "sixth";
	public static final String SEVENTH = "seventh";
	public static final String EIGHTH = "eighth";

	// Database path and name
	private static final String DATABASE_PATH = "/data/data/com.telsoft.kqxs/databases/";
	private static final String DATABASE_NAME = "KQXS_database";

	// Database Version
	private static final int DATABASE_VERSION = 2;

	private static final String TAG = "DBAdapter";

	private static final String PROVINCE_TABLE_CREATE = "CREATE TABLE "

	+ PROVINCE_TABLE + "(" + PROVINCE_PRIMARY_KEY + " TEXT, " + PROVINCE_CODE
			+ " TEXT, " + PROVINCE_NAME + " TEXT);";

	private static final String LOTTERY_TABLE_CREATE = "CREATE TABLE "
			+ LOTTERY_TABLE + " (" + LOTTERY_PRIMARY_KEY

			+ " TEXT, " + PROVINCE_CODE + " TEXT, " + OPEN_DATE + " TEXT, "
			+ SPECIAL_1 + " TEXT, " + FIRST + " TEXT, " + SECOND + " TEXT, "
			+ THIRD + " TEXT, " + FOURTH + " TEXT, " + FIFTH + " TEXT, "
			+ SIXTH + " TEXT, " + SEVENTH + " TEXT, " + EIGHTH + " TEXT)";

	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DBAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {

			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		// CREAT TABLE PROVINCE AND LOTTERY
		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(PROVINCE_TABLE_CREATE);
				db.execSQL(LOTTERY_TABLE_CREATE);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");

			db.execSQL("DROP TABLE IF EXISTS " + PROVINCE_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + LOTTERY_TABLE);
			onCreate(db);
		}
	}

	/**
	 * 
	 * @return
	 * @throws SQLException
	 */
	public DBAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	/**
	 * 
	 */
	public void close() {
		DBHelper.close();
	}

	/**
	 * Insert a province into the Province table
	 * 
	 * @param code
	 * @param id
	 * @return Name
	 */
	public long insertProvince(String id, String code, String name) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(PROVINCE_PRIMARY_KEY, id);
		initialValues.put(PROVINCE_CODE, code);
		initialValues.put(PROVINCE_NAME, name);
		return db.insert(PROVINCE_TABLE, null, initialValues);
	}

	/**
	 * @param id
	 * @param code
	 * @param special_1
	 * @param first
	 * @param second
	 * @param third
	 * @param fourth
	 * @param fifth
	 * @param sixth
	 * @param seventh
	 * @param eighth
	 * @return
	 */

	public long insertLotteryResult(String id, String code, String openDate,
			String special_1, String first, String second, String third,
			String fourth, String fifth, String sixth, String seventh,
			String eighth) {

		ContentValues initialValues = new ContentValues();

		initialValues.put(LOTTERY_PRIMARY_KEY, id);
		initialValues.put(PROVINCE_CODE, code);
		initialValues.put(OPEN_DATE, openDate);
		initialValues.put(SPECIAL_1, special_1);
		initialValues.put(FIRST, first);
		initialValues.put(SECOND, second);
		initialValues.put(THIRD, third);
		initialValues.put(FOURTH, fourth);
		initialValues.put(FIFTH, fifth);
		initialValues.put(SIXTH, sixth);
		initialValues.put(SEVENTH, seventh);
		initialValues.put(EIGHTH, eighth);
		return db.insert(LOTTERY_TABLE, null, initialValues);

	}

	// ---retrieves all the Prize---
	public Cursor getAllPrize() {
		return db.query(LOTTERY_TABLE, new String[] { LOTTERY_PRIMARY_KEY,
				PROVINCE_CODE, OPEN_DATE, SPECIAL_1, FIRST, SECOND, THIRD,
				FOURTH, FIFTH, SIXTH, SEVENTH, EIGHTH }, null, null, null,
				null, null, null);
	}

	public Cursor getPrize(String id) {
		return db.rawQuery("select * from Lottery where id = ?",
				new String[] { id });
	}

	public Cursor doQuery(String query) {

		// Query
		Cursor cursor = db.rawQuery(query, null);
		cursor.moveToFirst();

		return cursor;
	}

	public Cursor getAllProvince() {
		return db.query(PROVINCE_TABLE, new String[] { PROVINCE_PRIMARY_KEY,
				PROVINCE_CODE, PROVINCE_NAME }, null, null, null, null, null);
	}

	/** Check if the database already exist, return true if it exists **/
	public boolean checkDataBase() {
//		SQLiteDatabase checkDB = null;
//		try {
			// checkDB = SQLiteDatabase.openDatabase(DATABASE_NAME, null,
			// SQLiteDatabase.OPEN_READONLY);
			File dbFile = new File(DATABASE_PATH + DATABASE_NAME);
			return dbFile.exists();

		// } catch (SQLiteException e) {
		// // database does't exist yet.
		// }
		// if(checkDB != null){
		// //context.deleteDatabase(DATABASE_NAME);
		// //open();
		// checkDB.close();
		// }
		// return checkDB != null ? true : false;
	}

	public void removeProvinceTable(String id, String code, String name) {
		doQuery("delete from " + PROVINCE_TABLE + " where "
				+ PROVINCE_PRIMARY_KEY + " = '" + id + "'" + "and "
				+ PROVINCE_CODE + " = '" + code + "'" + "and " + PROVINCE_NAME
				+ " = '" + name + "'");
	}
}